
import java.sql.*;   // methods to process SQL commands
import java.util.ArrayList;
import java.util.Collections;

import javax.sql.*;  // ditto
//import org.postgresql.jdbc3.Jdbc3PoolingDataSource; // connection via JDBC to postgrSQL
import java.io.IOException;   // enable IOException objects & handling
import java.io.PrintWriter;   // enable PrintWriter (so can use .println())
import javax.servlet.http.*;  // provides servlet functionality
import javax.servlet.*;	      // ditto
import javax.*;


public class BrowseByTitle extends HttpServlet
{
	//private static DataSource ds;	// contains DB and connection data; see init()
	private Connection connection;
	//private static StringBuffer buf;
	//private static ArrayList<String> genres;
	private  int startIndex = 0;
	private boolean lastPage = false;
	
	public  String issueQuery(HttpSession session, String goTo)
    {	
		MovieList movie_list = (MovieList)session.getAttribute(Constants.MovieListAttributeName);
		
		//ArrayList<String> genres = new ArrayList<String>();
		StringBuffer buf2 = new StringBuffer();

		// see the API doc at java.sun.com for details
		try
		{
			// get and build string of test DB's metadata
			ResultSet result = movie_list.getMovieList();
			int start = movie_list.getStart();
			//buf2.append("START INDEX BEFORE ANY CHANGES:  " + movie_list.getStart());
			if(goTo != null && goTo.length()>0)
			{
				if( movie_list.getState().equals("lastPage"))
				{
				
					int sizeOfList = movie_list.getSizeOfMovieList();	
					//buf2.append("RESULT OF GOING BACK FROM LAST PAGE: sizeOfList: " + sizeOfList);
					if(sizeOfList%20 == 0)
					{	//buf2.append("size is mod by 20");
						movie_list.setStartIndex(sizeOfList - 40);
						int shouldStartAt = sizeOfList -40;
						//buf2.append("movie list should be start at : " + shouldStartAt +"<br>");
						//buf2.append("movie list actually starting at : " + movie_list.getStart() +"<br>");
					}
					else
					{
						int index = 0;
						while(index < sizeOfList)
						{
							index += 20;
						}
						movie_list.setStartIndex(index-40);
						
						if(index-40 != movie_list.getStart())
								buf2.append("start index does not match changes");
						
						//buf2.append("CHANGED START INDEX: " + movie_list.getStart());
					}
					movie_list.setState("middlePage");
				}
				else if(goTo.equals(Constants.PREV))
				{
					//buf2.append("before state: " + movie_list.getState());
					//buf2.append("before - GOING TO PREVIOUS!! SHOULD START AT: " + movie_list.getStart() + "<br><br>");
					//int temp = start - 40;
					movie_list.setStartIndex(movie_list.getStart() - 40);
					//buf2.append("after - GOING TO PREVIOUS!! SHOULD START AT: " + movie_list.getStart()+ "<br><br>");
					movie_list.setState("middlePage");
				}
				
				if(movie_list.getStart() < 0 || goTo.equals(Constants.BEGIN))
				{
					//buf2.append("starting list again at zero");
					movie_list.setState("firstPage");
					movie_list.setStartIndex(0);
				}
				if(goTo.equals(Constants.NEXT))
				{
					//buf2.append("GOING NEXT, starting with index: " + movie_list.getStart());
					movie_list.setState("middlePage");
				}
				
			}
			
			result.absolute(movie_list.getStart());
			
			startIndex = movie_list.getStart();
			//buf2.append("Starting at: " + startIndex + "<br><br>");
			// add table's contents to buffered string
			
			int counter = 0;
			while(counter < 20 && result.next() )
			{
				 buf2.append("<TR valign=top>");
				 buf2.append("<TD width=150><div class=\"wpmd\">");
				 buf2.append("<div>");
				 buf2.append("<input type=\"radio\" name=\"movieId\" value=\"" + result.getInt(1) + "\"> " + "<a href=\"SingleMovieTitle?id="+result.getInt(1)+"\"> " +result.getString(2) + "</a>");
				 buf2.append("</div>");
				 buf2.append("</TD>");
				
			
				 buf2.append("<TD width=100><div class=\"wpmd\">");
				 buf2.append("<div>");
				 buf2.append( result.getInt(3));
				 buf2.append("</div>");
				 buf2.append("</TD>");
				
				
				 buf2.append("<TD width=150><div class=\"wpmd\">");
				 buf2.append("<div>");
				 buf2.append(result.getString(7) + " "+ result.getString(4));
				 buf2.append("</div>");
				 buf2.append("</TD>");
				
				String genreAssociated = findGenres(Integer.toString(result.getInt(1)));
				//genres
				 buf2.append("<TD width=150><div class=\"wpmd\">");
				 buf2.append("<div>");
				 buf2.append(genreAssociated);
				 buf2.append("</div>");
				 buf2.append("</TD>");
				

				String stars = findStars(Integer.toString(result.getInt(1)));
				//movie stars
				 buf2.append("<TD width=200><div class=\"wpmd\">");
				 buf2.append("<div>");
				 buf2.append(stars);
				 buf2.append("</div>");
				 buf2.append("</TD>");
				
				 buf2.append("</TR>");
				 
				 counter++;
				 		
			}
			
			if(!result.isAfterLast() && counter == 20)
			{
				//buf2.append("middle page or beginning page");
				movie_list.setStartIndex(result.getRow());
				lastPage = false;
			}
			if((result.isLast() &&  counter == 20) || result.isAfterLast())
			{		
				int tempInt = movie_list.getStart() + 20;
				movie_list.setStartIndex(tempInt);
				//buf2.append("last page! Ending list at: " + tempInt);
				//buf2.append("last page set start index: " + movie_list.getStart());
				lastPage = true;
				movie_list.setState("lastPage");
			}
		
			//buf2.append("Ending list at : " + movie_list.getStart());
			// query sent; close connection to DB
			connection.close();
		}
		catch (SQLException sqle)
		{
			 buf2.append("SQL exception occured: " + sqle.getMessage());
		}		
		session.setAttribute(Constants.MovieListAttributeName, movie_list);
		// return buffered string of text
		return  buf2.toString();
	}
	
	public String findGenres(String movieId) throws SQLException
	{
		Statement select4 = connection.createStatement();
		ResultSet resultGenresID = select4.executeQuery("Select genre_id from genres_in_movies where movie_id = " + movieId);
		String temp = "";
		while(resultGenresID.next())
		{
			Statement select5 = connection.createStatement();
			ResultSet resultGenres = select5.executeQuery("Select * from genres where id = " + resultGenresID.getInt(1));
			if(resultGenres.next())
				temp += resultGenres.getString(2) + " <br>";
		}
		if(temp.equals(""))
			return "No genres";
		return temp;	
	}
	public  String findStars(String movieId) throws SQLException
	{
		Statement select2 = connection.createStatement();
		ResultSet resultStarsID = select2.executeQuery("Select star_id from stars_in_movies where movie_id = " + movieId);
		String temp = "";
		
		while(resultStarsID.next())
		{
			Statement select3 = connection.createStatement();
			ResultSet resultStars = select3.executeQuery("Select * from stars where id = " + resultStarsID.getInt(1));
			if(resultStars.next())
			{
				temp += "<a href=\"SingleStarPage?id=" + resultStars.getInt(1) + "\"> " + resultStars.getString(2) + " " + resultStars.getString(3) + " </a><br>";				
			}
		}
	
		if(temp.equals(""))
			return "No movie stars";
		return temp;
	}
	// Print HTML for Web page heading
	public void doGet(HttpServletRequest request, HttpServletResponse response)
		throws IOException, ServletException
	{
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		StringBuffer buf = new StringBuffer();
	
		if (!Bridge.hasBeenToMainPage(request.getSession(), response.getWriter()))
		{
			return;
		}
		
		String goTo = "";
		try
		{
			HttpSession session = request.getSession();
			Class.forName("org.postgresql.Driver").newInstance();
			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost/fabflixs", "testuser", "testpass");
			
			
			if(request.getParameter("goTo") != null)
				goTo = request.getParameter("goTo");
			else
			{
			
				MovieList movieList = (MovieList)session.getAttribute(Constants.MovieListAttributeName);
				if(movieList == null || !movieList.getMovieListType().equals(Constants.BROWSE_BY_TITLE) || (request.getParameter("order") == null && request.getParameter("goTo") == null))
				{
					//check what type of ordering here:
					//buf.append("building new list from browse by title <br><br>");
					Statement select20 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
					ResultSet resultMovieList = select20.executeQuery("Select* from movies ORDER BY title, year DESC");
					movieList = new MovieList();
					movieList.setRS(resultMovieList);
					movieList.setMovieListType(Constants.BROWSE_BY_TITLE);
					movieList.setState("firstPage");
					session.setAttribute(Constants.MovieListAttributeName, movieList);
					startIndex = movieList.getStart() ;
					//buf.append("starting new movie list of type: " + movieList.getMovieListType() + " with starting index: " + movieList.getStart());
					
				}
				else if(request.getParameter("order") != null && (request.getParameter("order")).length() > 0)
				{
					//MovieList movie_list = (MovieList) session.getAttribute(Constants.MovieListAttributeName);
					//buf.append("REORDER <br><br>");
					if(request.getParameter("order").equals(Constants.ORDER_BY_TITLE))
					{
						if(movieList.getOrderType().equals(Constants.ORDER_BY_TITLE))
							movieList.reverseOrder();
						else
						{
							movieList.setOrderType(Constants.ORDER_BY_TITLE);
							//movie_list.order = Constants.ASCENDING;
						}
						
						Statement select21 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
						ResultSet resultMovieList = select21.executeQuery("Select * from movies ORDER BY title " + movieList.getOrder() + ", year DESC");
						movieList.setRS(resultMovieList);
						movieList.setStartIndex(0);
						
					}
					else if(request.getParameter("order").equals(Constants.ORDER_BY_YEAR) )
					{
						if(movieList.getOrderType().equals(Constants.ORDER_BY_YEAR))
							movieList.reverseOrder();
						else
						{
							movieList.setOrderType(Constants.ORDER_BY_YEAR);
							//movie_list.order = Constants.ASCENDING;
						}
						
						Statement select22 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
						ResultSet resultMovieList = select22.executeQuery("Select * from movies ORDER BY year " + movieList.getOrder());
						movieList.setRS(resultMovieList);
						movieList.setStartIndex(0);
					}
					else if(request.getParameter("order").equals(Constants.ORDER_BY_DIRECTOR) )
					{
						if(movieList.getOrderType().equals(Constants.ORDER_BY_DIRECTOR))
							movieList.reverseOrder();
						else
						{
							movieList.setOrderType(Constants.ORDER_BY_DIRECTOR);
							//movie_list.order = Constants.ASCENDING;
						}
						
						Statement select23 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
						ResultSet resultMovieList = select23.executeQuery("Select * from movies ORDER BY director_last_name " + movieList.getOrder() + ", year DESC");
						movieList.setRS(resultMovieList);
						movieList.setStartIndex(0);
					}
					
					if(movieList.getStart() == 0)
						movieList.setStartIndex(0);
					
					movieList.setState("firstPage");
					startIndex = movieList.getStart();
					
					session.setAttribute(Constants.MovieListAttributeName, movieList);
				}
			}
			
			
		

				
			
			//creating form
			buf.append("<form name=\"movieTitle\" action=\"DvdProcessor\" method=\"POST\">");
			//out.println("<div align=\"left\"><br>");
			
			//making table
		
			buf.append("<div><TABLE bgcolor=\"#FFFFFF\" border=1 bordercolorlight=\"#C0C0C0\" bordercolordark=\"#808080\">");
			
			buf.append("<TR valign=top>");
		
		
			buf.append("<TD width=150><div class=\"wpmd\">");
			buf.append("<div>");
			buf.append("<a href=\"BrowseByTitle?order=5\"><B>TITLE</B></a>");
			buf.append("</div>");
			buf.append("</TD>");
			
			
			buf.append("<TD width=100><div class=\"wpmd\">");
			buf.append("<div>");
			buf.append("<a href=\"BrowseByTitle?order=6\"><B>YEAR</B></a>");
			buf.append("</div>");
			buf.append("</TD>");
			
			buf.append("<TD width=150><div class=\"wpmd\">");
			buf.append("<div>");
			buf.append("<a href=\"BrowseByTitle?order=7\"><B>DIRECTOR</B></a>");
			buf.append("</div>");
			buf.append("</TD>");
			
			buf.append("<TD width=150><div class=\"wpmd\">");
			buf.append("<div>");
			buf.append("<B>GENRE</B>");
			buf.append("</div>");
			buf.append("</TD>");
			
			buf.append("<TD width=200><div class=\"wpmd\">");
			buf.append("<div>");
			buf.append("<B>MOVIE STARS</B>");
			buf.append("</div>");
			buf.append("</TD>");
			buf.append("</TR>");
			
			buf.append(issueQuery(session, goTo));
			
			buf.append("<TR valign=top>");
	
			buf.append("<TD colspan=5 align=center width=200><div class=\"wpmd\">");
			buf.append("<div id=\"AddToCart\" \"><input type=\"image\" name=\"AddingToShoppingCar\" src=\"../images/AddToCart.png\"></div>");
			buf.append("</TD>");
			buf.append("</TR>");
			buf.append("</form>");
			
			if(lastPage)
			{
				buf.append("<TR valign=top>");
				buf.append("<TD colspan=5 align=left ><div class=\"wpmd\">");
				buf.append("<a href=\"BrowseByTitle?goTo=2\">");
				buf.append("LAST PAGE!!");
				buf.append("<div id=\"Prev\" \"><input type=\"image\" name=\"Prev\" src=\"../images/Prev.png\"></div></a>");
				buf.append("</TD>");
			}
			else if(startIndex != 0 && !lastPage)
			{
				//row for prev and next button
				buf.append("<TR valign=top>");
				buf.append("<TD colspan=3 align=left ><div class=\"wpmd\">");
				buf.append("<a href=\"BrowseByTitle?goTo=2\">");
				buf.append("<div id=\"Prev\" \"><input type=\"image\" name=\"Prev\" src=\"../images/Prev.png\"></div></a>");
				buf.append("</TD>");
			
				
			
				buf.append("<TD colspan=2 align=right ><div class=\"wpmd\">");
				buf.append("<a href=\"BrowseByTitle?goTo=1\">");
				buf.append("<div id=\"Next\" \"><input type=\"image\" name=\"Next\" src=\"../images/Next.png\"></div></a>");
				buf.append("</TD>");
			}
			else if(startIndex == 0 && !lastPage)
			{
				
				buf.append("<TD colspan=5 align=right ><div class=\"wpmd\">");
				buf.append("<a href=\"BrowseByTitle?goTo=1\">");
				buf.append("<div id=\"Next\" \"><input type=\"image\" name=\"Next\" src=\"../images/Next.png\"></div></a>");
				buf.append("</TD>");
				
			}
			
			buf.append("</TR>");
			buf.append("</TABLE>");
			buf.append("</form>");
			
			lastPage = false; //resetting last page boolean
			
			out.println(Template.getTemplateHTML("Browse By Title", buf.toString(), Constants.straightToCart,
					session ));
		}
		catch (SQLException sqle)
		{
			buf.append("SQL exception occured: " + sqle.getMessage());
			out.print("SQL exception occured: " + sqle.getMessage());
		}	
		catch (ClassNotFoundException cnfe)
		{
			buf.append("ClassNotFound exception occured: " + cnfe.getMessage());
			out.print("ClassNotFound exception occured: " + cnfe.getMessage());
		}
		catch (InstantiationException ie)
		{
			buf.append("Instantiation exception occured: " + ie.getMessage());
			out.print("Instantiation exception occured: " + ie.getMessage());
		}
		catch (IllegalAccessException iae)
		{
			buf.append("IllegalAccess exception occured: " + iae.getMessage());
			out.print("IllegalAccess exception occured: " + iae.getMessage());
		}
		catch (Exception e)
		{
			buf.append("Exception: " + e.getMessage());
			out.print("Exception: " + e.getMessage());
		}
	
	}
	
	// Upon init of servlet, open the moviedb database
	public void init() 
	{
	
	}

	
}


